PHP | MySQL ( Creating Database )
How to connect PHP with MySQL Database?
PHP 5 and later can work with a MySQL database using:
MySQLi extension.
PDO (PHP Data Objects).
Difference Between MySQLi and PDO
PDO works on 12 different database systems, whereas MySQLi works only with MySQL databases.
Both PDO and MySQLi are object-oriented, but MySQLi also offers a procedural API.
If at some point of development phase, the user or the development team wants to change the database then it is easy to that in PDO than MySQLi as PDO supports 12 different database systems.He would have to only change the connection string and a few queries. 
With MySQLi,he will need to rewrite the entire code including the queries.

There are three ways of working with MySQl and PHP
MySQLi (object-oriented)
MySQLi (procedural)
PDO
Connecting to MySQL database using PHP
There are 3 ways in which we can connect to MySQl from PHP as listed above and described below:

Using MySQLi object-oriented procedure: We can use the MySQLi object-oriented procedure to establish a connection to MySQL database from a PHP script.
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = new mysqli($servername, $username, $password);
// Checking connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>
Output:

Explanation: We can create an instance of the mysqli class providing all the necessary details required to establish the connection such as host, username, password etc. 
If the instance is created successfully then the connection is successful otherwise there is some error in establishing connection.


Using MySQLi procedural procedure : There is also a procedural approach of MySQLi to establish a connection to MySQL database from a PHP script as described below.
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = mysqli_connect($servername, $username, $password);
// Checking connection
if (!$conn) {
    die("Connection failed: " . 
mysqli_connect_error());
}
echo "Connected successfully";
?>
Output:


          
          
          
          
            
Explanation: In MySQLi procedural approach instead of creating an instance we can use the mysqli_connect() function available in PHP to establish a connection. 
This function takes the information as arguments such as host, username , password , database name etc. 
This function returns MySQL link identifier on successful connecction or FALSE when failed to establish a connection.

Using PDO procedure: PDO stands for PHP Data Objects. 
That is, in this method we connect to the database using data objects in PHP as described below:
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    // setting the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>
Output:

Explanation:The exception class in PDO is used to handle any problems that may occur in our database queries. 
If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.

Closing A Connection
When we establish a connection to MySQL database from a PHP script , we should also disconnect or close the connection when our work is finished. 
Here we have described the syntax of closing the connection to a MySQL database in all 3 methods described above. 
We have assumed that the reference to the connection is stored in $conn variable.
Using MySQLi object oriented procedure
Syntax
$conn->close();

Using MySQLi procedural procedure
Syntax
mysqli_close($conn);

Using PDO procedure
Syntax
$conn = null;

The basic steps to create MySQL database using PHP are:
Establish a connection to MySQL server from your PHP script as described in this article.
If the connection is successful, write a SQL query to create a database and store it in a string variable.
Execute the query.
We have already learnt about establish a connection and creating variables in PHP. 
We can execute the query from our PHP script in 3 different ways as described below:

Using MySQLi Object-oriented procedure: If the MySQL connection is established using Object-oriented procedure then we can use the query() function of mysqli class to execute our query as described in the below syntax.
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating a connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
// Creating a database named newDB
$sql = "CREATE DATABASE newDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully with the name newDB";
} else {
    echo "Error creating database: " . $conn->error;
}
// closing connection
$conn->close();
?>

Note:Specify the three arguments servername, username and password to the mysqli object whenever creating a database.
Output:


Using MySQLi Procedural procedure:  If the MySQL connection is established using procedural procedure then we can use the mysqli_query() function of PHP to execute our query as described in the below syntax.
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = mysqli_connect($servername, $username, $password);
// Checking connection
if (!$conn) {
    die("Connection failed: " . 
mysqli_connect_error());
}
// Creating a database named newDB
$sql = "CREATE DATABASE newDB";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully with the name newDB";
} else {
    echo "Error creating database: " . 
mysqli_error($conn);
}
// closing connection
mysqli_close($conn);
?>

Output:


Using PDO procedure:  If the MySQL connection is established using PDO procedure then we can execute our query as described in the below syntax.
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
    $conn = new PDO("mysql:host=$servername;dbname=newDB", $username, $password);
    // setting the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE newDB";
    // using exec() because no results are returned
    $conn->exec($sql);
    echo "Database created successfully with the name newDB";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }
$conn = null;
?>

Note:The exception class in PDO is used to handle any problems that may occur in our database queries. 
If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.
Output:


Connecting to MySQL with PHP
https://www.taniarascia.com/create-a-simple-database-app-connecting-to-mysql-with-php/
CRUD application, which stands for Create, Read, Update, Delete.
A quick example of a CRUD application would be a database of employees for a company. 
From the control panel, an admin would be about to add a new employee (create), view a list of employees (read), change an employee's salary (update) or remove a fired employee from the system (delete). 
Goals
Connect to a MySQL database with PHP using the PDO (PHP Data Objects) method.
Create an installer script that creates a new database and a new table with structure.
Add entries to a database using an HTML form and prepared statements.
Filter database entries and print them to the HTML document in a table.
All the code throughout this article can be found on GitHub.
View on GitHub
Part 2: Update and Delete
Step 1: Building the front end
We don't have a database set up or anything, but we're just going to set up the HTML front end in order to be prepared to start interacting with that data.
Index page and template partials
Our main/home page will be located at index.php, so create that file in your public/ directory.
public/index.php
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta http-equiv="x-ua-compatible" content="ie=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <title>Simple Database App</title>
    <link rel="stylesheet" href="css/style.css" />
  </head>
  <body>
    <h1>Simple Database App</h1>
    <ul>
      <li>
        <a href="create.php"><strong>Create</strong></a> - add a user
      </li>
      <li>
        <a href="read.php"><strong>Read</strong></a> - find a user
      </li>
    </ul>
  </body>
</html>
Right now, all we have is a basic HTML skeleton that will link to our create and read pages. 
Here's what it looks like:

Create a templates/ directory in public, and make a header.php and footer.php. 
You'll take everything from the <h1> tag and up and put it in the header.
public/templates/header.php
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta http-equiv="x-ua-compatible" content="ie=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <title>Simple Database App</title>
    <link rel="stylesheet" href="css/style.css" />
  </head>
  <body>
    <h1>Simple Database App</h1>
  </body>
</html>
And here's the footer.
public/templates/footer.php
</body>
</html>
All that remains in index.php at this point are the links to our two other pages.
public/index.php
<ul>
  <li>
    <a href="create.php"><strong>Create</strong></a> - add a user
  </li>
  <li>
    <a href="read.php"><strong>Read</strong></a> - find a user
  </li>
</ul>
We want to include the header and footer code in all our front end pages, so we'll be using a PHP include function to pull that code in.
public/index.php
<?php include "templates/header.php"; ?>
<ul>
  <li>
    <a href="create.php"><strong>Create</strong></a> - add a user
  </li>
  <li>
    <a href="read.php"><strong>Read</strong></a> - find a user
  </li>
</ul>
<?php include "templates/footer.php"; ?>
Now the front end of our index file looks the same as before, but we have the reusable layout code that we can use in our other pages.
Add a new user page
Now we're going to make a file called create.php back in our public/ directory. 
This will be the page we use to add a new user to the database. 
We'll start the file with our header and footer loaded in.
public/create.php
<?php include "templates/header.php"; ?>
    <?php include "templates/footer.php"; ?>
I'm going to create a simple form here that gathers the first name, last name, email address, age, and location of a new user.
public/create.php
<?php include "templates/header.php"; ?>
<h2>Add a user</h2>
    <form method="post">
    	<label for="firstname">First Name</label>
    	<input type="text" name="firstname" id="firstname">
    	<label for="lastname">Last Name</label>
    	<input type="text" name="lastname" id="lastname">
    	<label for="email">Email Address</label>
    	<input type="text" name="email" id="email">
    	<label for="age">Age</label>
    	<input type="text" name="age" id="age">
    	<label for="location">Location</label>
    	<input type="text" name="location" id="location">
    	<input type="submit" name="submit" value="Submit">
    </form>
    <a href="index.php">Back to home</a>
    <?php include "templates/footer.php"; ?>
create a css/ folder and make style.css. 
CSS and style is not a focus of this article, but I'm going to add a line of CSS code to make the forms easier to read.
public/css/style.css
label {
  display: block;
  margin: 5px 0;
}

We have not specified a form action, so pressing the submit button will perform the action on the same page. 
Since we haven't written any PHP code to process the form yet, it won't do anything.
Query users page
Finally, we're going to create our read.php file, which will query the list of users by a parameter (in this case, location) and print out the results.
Again, we'll start with the header and footer code.
public/read.php
<?php include "templates/header.php"; ?>
    <?php include "templates/footer.php"; ?>
Then we'll add a small form for searching for users by location.
public/read.php
<?php include "templates/header.php"; ?>
    <h2>Find user based on location</h2>
    <form method="post">
    	<label for="location">Location</label>
    	<input type="text" id="location" name="location">
    	<input type="submit" name="submit" value="View Results">
    </form>
    <a href="index.php">Back to home</a>
    <?php include "templates/footer.php"; ?>

Now you have all the front end code set up and we can start. 
Here's everything you should have so far.
public/
|-- css/
|   |-- style.css
|-- templates/
|   |-- header.php
|   |-- footer.php
|-- index.php
|-- create.php
|-- read.php
Step 2: Initializing the database
Now that we have a front end, we're going to create the database. 
We could do this through the GUI of SequelPro or whatever database manager we're using, but I want to show you how to do it with actual SQL statements and PHP in an installer script.

SQL (Structured Query Language) is a language used to communicate with a database.

First, let's get into the database. 
Here's the login page for the front end of our database.

Your host will be localhost or 127.0.0.1, which translate to the same thing for our purposes. 
Username and password will both be root. 
Entering that information in, you should be able to enter localhost.

Create a directory called data/ and create a file called init.sql. 
This will be our database initializing code.
data/init.sql
CREATE DATABASE test;
  use test;
  CREATE TABLE users (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL,
    age INT(3),
    location VARCHAR(50),
    date TIMESTAMP
  );
SQL is a relatively straightforward code, so even if you've never seen it before, it should be easy to understand. 
Here's what the above means in plain English:
We're going to create a database called test. 
Then we're going to make sure we're using test for the rest of our code. 
In the test database, we'll create a table called users with 7 fields inside - id, firstname, lastname, email, age, location, and date. 
Next to each field is more information, options, and settings for each.
INT() - this is an Integer. 
We specified INT(11), which means up to 11 characters
AUTO_INCREMENT - this is a number that will automatically increase with each entry.
VARCHAR() - meaning Variable Character, this is a string that can contain letters and numbers. 
The number inside is the max amount of characters allowed.
TIMESTAMP - this will add the current time in YYYY-MM-DD HH:MI:SS format by default.
Testing the SQL query
If you really want to see what this code will do before we create the installer, you can paste it into the Query section of your database program and Run Selection.

If you did that, you'll see I now have a test database, a users table, and all the database structure.

So we know our SQL works properly and has no errors. 
If you did that, delete the database because we're going to start over and do it through the script.
Using PDO to connect to a database
We're going to use PDO (PHP Data Objects) to connect to the database. 
The other major option is MySQLi. 
The critical difference between the two is that you can use PDO to connect to any number of databases, and mysqli code will only work with MySQL. 
Although we're using a MySQL database, PDO is more extendable in the future, and generally the preferred choice for new projects. 
So let's create that connection.
Create a file called install.php in the root of your directory.
We'll create a new PDO() object and place it into a variable named $connection.
install.php
$connection = new PDO();
The PDO object will ask for four parameters:
DSN (data source name), which includes type of database, host name, database name (optional)
Username to connect to host
Password to connect to host
Additional options
install.php
$connection = new PDO(data source name, username, password, options);
Here's how that ends up looking after we fill in all the parameters.
install.php
new PDO("mysql:host=localhost", "root", "root",
  array(
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  );
);
Now we're going to organize it a bit more by putting all our database information into variables.
install.php
$connection = new PDO("mysql:host=$host", $username, $password, $options);
We'll create a config.php file that contains all the variables we can refer from.
config.php
<?php
/**
  * Configuration for database connection
  *
  */
$host       = "localhost";
$username   = "root";
$password   = "root";
$dbname     = "test"; // will use later
$dsn        = "mysql:host=$host;dbname=$dbname"; // will use later
$options    = array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
              );
Here's what we have in our installer so far. 
We're pulling in the database variables via config.php using require, which is similar to an include, except we're explicitly stating that the file is necessary for the script to run.
install.php
require "config.php";
$connection = new PDO("mysql:host=$host", $username, $password, $options);
Now it's time to put that SQL code we created earlier to use. 
We'll be placing the contents of the data/init.sql file into a variable using the file_get_contents() function, and executing it with the exec() function.
$sql = file_get_contents("data/init.sql");
$connection->exec($sql);
At this point, we're going to want to use Exceptions to attempt to run the script and catch errors. 
We'll do this by putting all our code in a try/catch block, which looks like this:
try {
// code to execute
} catch() {
// exception
}
Let's put our database code in the try block, and show our PDOException error message if something goes wrong trying to set up the database. 
Here's the final code for the installer.
install.php
<?php
/**
  * Open a connection via PDO to create a
  * new database and table with structure.
  *
  */
require "config.php";
try {
  $connection = new PDO("mysql:host=$host", $username, $password, $options);
  $sql = file_get_contents("data/init.sql");
  $connection->exec($sql);
  echo "Database and table users created successfully.";
} catch(PDOException $error) {
  echo $sql . "<br>" . $error->getMessage();
}
To run the install, just navigate to your install.php file on the front end.

If you didn't delete your database from our test before, you might get this error, in which case you'll need to delete the database for the installer to run.

Congratulations, you just made an installer script to set up a new database and table with structure!
Step 3: Adding a new user
Now, we're finally going to write some code to make those forms do something.
In PHP, when you submit a form, all the inputs are placed into a $_POST array. 
So my <input type="text" name="firstname"> will translate to $_POST['firstname'] for us to work with.
We're going to go back to the public/create.php file. 
Right now, it's just a form with a header and footer being pulled in. 
The new code we write will be added to the top of the file.
First, we're going to tell this code only to run if the form has been submitted.
if (isset($_POST['submit'])) {}
Just like with the installer, we're going to require our configuration file, and use a try/catch Exception to connect to the database.
If you'll notice, in the install.php script I was only connecting to mysql:host=$host in the first parameter (DSN). 
I didn't specify a database name, because we were creating the database in the file. 
Now that the database (named test) is created, I'm adding that to the first parameter. 
$dsn is set to mysql:host=$host;dbname=$dbname.
if (isset($_POST['submit'])) {
  require "../config.php";
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    // insert new user code will go here
  } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }
}
Let's create an array with all our submitted form values.
$new_user = array(
  "firstname" => $_POST['firstname'],
  "lastname"  => $_POST['lastname'],
  "email"     => $_POST['email'],
  "age"       => $_POST['age'],
  "location"  => $_POST['location']
);

We can use regular $_POST variables without further sanitization here because we're submitting to the database with prepared statements.

Now, the SQL code we're going to want to execute will look like this: INSERT INTO tablename (n) values (:n). 
In our specific case, it will be the below code.
INSERT INTO users (firstname, lastname, email, age, location) values (:firstname, :lastname, :email, :age, :location)
We could write out that code by hand and add each value every time we add one, but then we're updating things in three places and it becomes a pain. 
I learned a handy snippet of code from JeffreyWay of Laracasts to simplify that process.
We're going to use sprintf, which allows us to do the following: INSERT INTO x (y) values (:z).
$sql = sprintf(
    "INSERT INTO %s (%s) values (%s)",
    "users",
    implode(", ", array_keys($new_user)),
    ":" . implode(", :", array_keys($new_user))
);
That code will print out the exact same thing, without having to write it multiple times. 
Now we'll just prepare and execute the code.
$statement = $connection->prepare($sql);
$statement->execute($new_user);
Here is the full code inside our try block.
$connection = new PDO($dsn, $username, $password, $options);
$new_user = array(
  "firstname" => $_POST['firstname'],
  "lastname"  => $_POST['lastname'],
  "email"     => $_POST['email'],
  "age"       => $_POST['age'],
  "location"  => $_POST['location']
);
$sql = sprintf(
    "INSERT INTO %s (%s) values (%s)",
    "users",
    implode(", ", array_keys($new_user)),
    ":" . implode(", :", array_keys($new_user))
);
$statement = $connection->prepare($sql);
$statement->execute($new_user);
Now the form is all ready to send. 
I'm going to fill out my information and submit it.

Going into my MySQL, if all went well, I can now view my entry in the database!

Excellent! There's just one more thing I want to do. 
Right now, after submitting the form, there's nothing letting me know that the user was submitted successfully.
Escaping HTML
Since in this case we're going to print out a $_POST variable to the HTML, we need to properly convert the HTML characters, which will aid in preventing XSS attacks.
Let's create a new file called common.php in the root of your project. 
Inspiration for this function and filename came from Jon's PHP blog tutorial. 
This is a file that can be used to store functions for later use. 
I'm only going to use it for one function today - an HTML escaping function.
common.php
<?php
/**
  * Escapes HTML for output
  *
  */
function escape($html) {
  return htmlspecialchars($html, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
}
With this function, we can wrap any variable in the escape() function, and the HTML entities will be protected.
Back in public/create.php, add a require "common.php";. 
Now I'm just going to add this if statement below my header and above the "Add a user" title. 
It will check to see if a $_POST was submitted, and if a $statement was successful. 
If so, it will print a success message that includes the first name of the successfully added user.
<?php if (isset($_POST['submit']) && $statement) { ?>
  <?php echo escape($_POST['firstname']); ?> successfully added.
<?php } ?>

And that's everything! Here's the final code to add a new user.
public/create.php
<?php
/**
  * Use an HTML form to create a new entry in the
  * users table.
  *
  */
if (isset($_POST['submit'])) {
  require "../config.php";
  require "../common.php";
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $new_user = array(
      "firstname" => $_POST['firstname'],
      "lastname"  => $_POST['lastname'],
      "email"     => $_POST['email'],
      "age"       => $_POST['age'],
      "location"  => $_POST['location']
    );
    $sql = sprintf(
"INSERT INTO %s (%s) values (%s)",
"users",
implode(", ", array_keys($new_user)),
":" . implode(", :", array_keys($new_user))
    );
    $statement = $connection->prepare($sql);
    $statement->execute($new_user);
  } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }
}
?>
<?php require "templates/header.php"; ?>
<?php if (isset($_POST['submit']) && $statement) { ?>
  > <?php echo $_POST['firstname']; ?> successfully added.
<?php } ?>
<h2>Add a user</h2>
<form method="post">
  <label for="firstname">First Name</label>
  <input type="text" name="firstname" id="firstname">
  <label for="lastname">Last Name</label>
  <input type="text" name="lastname" id="lastname">
  <label for="email">Email Address</label>
  <input type="text" name="email" id="email">
  <label for="age">Age</label>
  <input type="text" name="age" id="age">
  <label for="location">Location</label>
  <input type="text" name="location" id="location">
  <input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
Step 4: Viewing and filtering users
Here's the last step - the "read" of our CRUD app. 
We already created the front end in public/read.php.
Really quickly, let's add a small amount of CSS to our public/css/style.css file to make the tables legible once we create them.
public/css/style.css
table {
  border-collapse: collapse;
  border-spacing: 0;
}
td,
th {
  padding: 5px;
  border-bottom: 1px solid #aaa;
}
Now we're going to use the same requires from our new user page, as well as the try/catch block for connecting to the database.
public/read.php
if (isset($_POST['submit'])) {
  try {
    require "../config.php";
    require "../common.php";
    $connection = new PDO($dsn, $username, $password, $options);
        // fetch data code will go here
  } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }
}
?>
Now we'll write a SELECT SQL query. 
We're going to select all (*) from the users table, and filter by location.
$sql = "SELECT *
  FROM users
  WHERE location = :location";
Then we'll put our $_POST into a varable.
$location = $_POST['location'];
Prepare, bind, and execute the statement.
$statement = $connection->prepare($sql);
$statement->bindParam(':location', $location, PDO::PARAM_STR);
$statement->execute();
Finally, we'll fetch the result.
$result = $statement->fetchAll();
Here's the full try connection code.
$connection = new PDO($dsn, $username, $password, $options);
$sql = "SELECT *
FROM users
WHERE location = :location";
$location = $_POST['location'];
$statement = $connection->prepare($sql);
$statement->bindParam(':location', $location, PDO::PARAM_STR);
$statement->execute();
$result = $statement->fetchAll();
Great, now we have the whole process to retrieve the filtered data. 
All that's left is to print out the result.
Outside of the try/catch connection block and below the header, I'm going to insert the code for the table.
We'll check - if this is a POST request, and if the result of our query has more than 0 rows, open the table, loop through all the results, and close the table. 
If there are no results, display a message.
if (isset($_POST['submit'])) {
    	if ($result && $statement->rowCount() > 0) {
    		// open table
    		foreach ($result as $row) {
    			// table contents
    		}
    		// close table
    	} else 	{
    		// no results
    	}
    }
Here is the final code.
public/read.php
<?php
/**
  * Function to query information based on
  * a parameter: in this case, location.
  *
  */
if (isset($_POST['submit'])) {
  try {
    require "../config.php";
    require "../common.php";
    $connection = new PDO($dsn, $username, $password, $options);
    $sql = "SELECT *
    FROM users
    WHERE location = :location";
    $location = $_POST['location'];
    $statement = $connection->prepare($sql);
    $statement->bindParam(':location', $location, PDO::PARAM_STR);
    $statement->execute();
    $result = $statement->fetchAll();
  } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }
}
?>
<?php require "templates/header.php"; ?>
<?php
if (isset($_POST['submit'])) {
  if ($result && $statement->rowCount() > 0) { ?>
    <h2>Results</h2>
    <table>
      <thead>
<tr>
  <th>#</th>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Email Address</th>
  <th>Age</th>
  <th>Location</th>
  <th>Date</th>
</tr>
      </thead>
      <tbody>
  <?php foreach ($result as $row) { ?>
      <tr>
<td><?php echo escape($row["id"]); ?></td>
<td><?php echo escape($row["firstname"]); ?></td>
<td><?php echo escape($row["lastname"]); ?></td>
<td><?php echo escape($row["email"]); ?></td>
<td><?php echo escape($row["age"]); ?></td>
<td><?php echo escape($row["location"]); ?></td>
<td><?php echo escape($row["date"]); ?> </td>
      </tr>
    <?php } ?>
      </tbody>
  </table>
  <?php } else { ?>
    > No results found for <?php echo escape($_POST['location']); ?>.
  <?php }
} ?>
<h2>Find user based on location</h2>
<form method="post">
  <label for="location">Location</label>
  <input type="text" id="location" name="location">
  <input type="submit" name="submit" value="View Results">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
After adding a few entries, I can play around with it. 
I input the city.

And view the results.

Congratulations, you now have the very beginnings of a simple PHP database app without frameworks.
Conclusion
Now that you've learned how to create and read entries, check out part two!
Onto Part 2: Update and Delete
We went over a lot of valuable lessons in this tutorial, including but not limited to: connecting to a MySQL database with PDO, creating an installer script, inserting users into a database, selecting and printing out users from a database, and escaping printed HTML.
If this were a real world app, of course there would be more considerations to make. 
The backend would have to be password protected, which means you would make a login page and administrative users who are the only ones who have access to the app.
You would also add JavaScript validation to the front end of the site to ensure all required fields were being filled in, and using the correct type of characters for the input. 
(Although JavaScript validation is important, the most important part is that the back end that is receiving your data is protected).
Again, all the code from this article is available on GitHub if you got lost anywhere along the way. 
If you have suggestions on how to make the tutorial more clear or more secure, please don't hesitate to share! However, take into account this is meant to be a first look at the underlying code of database management, and not a full-fledged app.
View on GitHub
part two
We will learn how to modify existing entries (update) and permanently remove existing entires (delete).
If you haven't read part one but you're interested in learning how to manipulate data with PHP and MySQL, I highly recommend you check it out! Onto the tutorial.
Prerequisites
Everything you need to know to use and understand this tutorial is in Create a Simple CRUD Database App: Connecting to MySQL with PHP (Part 1: Create, Read). 
To reiterate, you must have:
A basic knowledge of HTML
A basic knowledge of PHP syntax and code structure
A local PHP and MySQL environment (MAMP, XAMPP, Vagrant, or other)
A database management program (Sequel Pro for Mac, SQLYog for Windows)
The codebase of the PDO App we created. 
(This link leads to the last commit prior to this article being written)
Goals
Create a page that lists all users with an edit button next to their name
Dynamically create a unique page for editing any user's data
Create a page that list all users with a delete button next to their name
Step 1: Set up the environment
If you're following along directly from part one, you will have all the code you need to start ready and functioning. 
If you did the tutorial a while ago and need to get everything set up again, it's all available and up to date in this GitHub repository. 
You can either download all the code as a zip and place it in your server root, or clone the repo if you know how to use Git.
Everything we will be editing is in the public/ folder, as all our initial setup is out of the way. 
Go ahead and get everything set up and working from the place we left off, and then we can continue.
One note, I'll be using the URL http:/db.dev URL I used the first time around, as .dev domains are no longer available locally in Chrome. 
Everything else will be the same.
Step 2: Create an editable list of all users
We left off in part one with create.php and read.php. 
Now we're going to create update.php.
In index.php, add a link to update.php.
public/index.php
<?php include "templates/header.php"; ?>
<ul>
    <li><a href="create.php"><strong>Create</strong></a> - add a user</li>
    <li><a href="read.php"><strong>Read</strong></a> - find a user</li>
    <li><a href="update.php"><strong>Update</strong></a> - edit a user</li>
</ul>
<?php include "templates/footer.php"; ?>

    
Now that the main view of our app links to the update.php, let's create the file.
The purpose of this file is to list all users in the database, and show an "Edit" link next to each user, which we'll be able to click on and edit each user individually. 
We will use most of the same code from read.php, except more simplified.
We will use a simple SELECT statement to get all users.
$sql = "SELECT * FROM users";
This is the simplest possible SQL command we can execute with PDO - simply select all users, prepare the statement, and store the result in $result.
$sql = "SELECT * FROM users";
$statement = $connection->prepare($sql);
$statement->execute();
$result = $statement->fetchAll();
Using that, we can built out our try/catch block at the top of update.php.
public/update.php
<?php
/**
  * List all users with a link to edit
  */
try {
  require "../config.php";
  require "../common.php";
  $connection = new PDO($dsn, $username, $password, $options);
  $sql = "SELECT * FROM users";
  $statement = $connection->prepare($sql);
  $statement->execute();
  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . 
"<br>" . $error->getMessage();
}
?>
Right below this code, we'll print the HTML table with the data from our SELECT statement.
public/update.php
<?php require "templates/header.php"; ?>
<h2>Update users</h2>
<table>
  <thead>
    <tr>
      <th>#</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Email Address</th>
      <th>Age</th>
      <th>Location</th>
      <th>Date</th>
    </tr>
  </thead>
  <tbody>
  <?php foreach ($result as $row) : ?>
    <tr>
      <td><?php echo escape($row["id"]); ?></td>
      <td><?php echo escape($row["firstname"]); ?></td>
      <td><?php echo escape($row["lastname"]); ?></td>
      <td><?php echo escape($row["email"]); ?></td>
      <td><?php echo escape($row["age"]); ?></td>
      <td><?php echo escape($row["location"]); ?></td>
      <td><?php echo escape($row["date"]); ?> </td>
  </tr>
  <?php endforeach; ?>
  </tbody>
</table>
<a href="index.php">Back to home</a>
Before anything will show up here, we'll have to go back to create.php and add a few users, which I just did for this example.
Here is what we have now in update.php:

    
Using HTTP query strings
Up until now, this is all the same stuff we covered in read.php. 
The interesting part that we'll add now is how we'll get to a page where we can edit each individual user.
Below Date in the thead, let's add a th for Edit.
<th>Edit</th>
Now in the tbody, we'll add a link that corresponds to this header for each user. 
In just a moment, we'll create a new file called update-single.php, which will be an edit page for each user. 
But how will update-single.php know if we're editing Richard or Gilfoyle?
We're going to tell update-single.php via a parameter in the URL which user to edit. 
Since we know id of each user is unique, and we can't guarantee other data such as first name and age will be unique, we can safely use id to identify each user.
Let's create a link that goes to update-single.php, and tack a question mark after the file name, which begins an HTTP query string. 
After this question mark, we can insert as many key/value pairs as we want in the URL.
If we want to access Dinesh, the user with an id of 3, our url will be update-single.php?id=3. 
We will get those values dynamically the same way we do to just print them out normally, except we'll embed it in the URL, like so.
<td><a href="update-single.php?id=<?php echo escape($row["id"]); ?>">Edit</a></td>
Here is the final code for update.php.
public/update.php
<?php
/**
  * List all users with a link to edit
  */
try {
  require "../config.php";
  require "../common.php";
  $connection = new PDO($dsn, $username, $password, $options);
  $sql = "SELECT * FROM users";
  $statement = $connection->prepare($sql);
  $statement->execute();
  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . 
"<br>" . $error->getMessage();
}
?>
<?php require "templates/header.php"; ?>
<h2>Update users</h2>
<table>
  <thead>
    <tr>
      <th>#</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Email Address</th>
      <th>Age</th>
      <th>Location</th>
      <th>Date</th>
      <th>Edit</th>
    </tr>
  </thead>
    <tbody>
    <?php foreach ($result as $row) : ?>
      <tr>
        <td><?php echo escape($row["id"]); ?></td>
        <td><?php echo escape($row["firstname"]); ?></td>
        <td><?php echo escape($row["lastname"]); ?></td>
        <td><?php echo escape($row["email"]); ?></td>
        <td><?php echo escape($row["age"]); ?></td>
        <td><?php echo escape($row["location"]); ?></td>
        <td><?php echo escape($row["date"]); ?> </td>
        <td><a href="update-single.php?id=<?php echo escape($row["id"]); ?>">Edit</a></td>
      </tr>
    <?php endforeach; ?>
    </tbody>
</table>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>

    
Now we have a list of all users with links to edit them. 
If you hover over each edit button, you'll see the id in the URL change to correspond to each user. 
However, the links don't go anywhere yet, so that's what we'll work on next.
Step 3: Modify an existing user
Editing an existing database entry is the trickiest of all four aspects of CRUD, and it's particularly difficult to find a modern, cohesive tutorial of any sort on how to do this with HTML and PHP, especially using the modern PDO method, so here we go!
First, we'll want to display the data we're going to edit, then we'll need to add functions to modify it.
In update.php, we created edit links that detect the id of the users and create a unique URL for each user. 
In update-single.php, we have to figure out which id is being loaded in, because all the edit pages will load and be routed through this single page.
Just as we've used the superglobal $_POST to detect what data has been posted through an HTML form, we'll use $_GET to retrieve information from the URL. 
It's important to note that sensitive data such as passwords should never be passed through the $_GET variable; however, for our simple purposes today, it will do just fine.
Let's create update-single.php, load in the required files, and make an if/else statement. 
We'll check if id is found in the URL, otherwise we'll just show a brief error message and close the script.
public/update-single.php
<?php
/**
  * Use an HTML form to edit an entry in the
  * users table.
  *
  */
require "../config.php";
require "../common.php";
if (isset($_GET['id'])) {
  echo $_GET['id']; // for testing purposes
} else {
    echo "Something went wrong!";
    exit;
}
?>
Now if I click on Dinesh...

    
The page will print out 3, as seen in the URL. 
Great! Now that we know that's working, we can use it to pull the data specifically for user id 3.
We're still working with SELECT statements here, so we're doing exactly what we did previously with the location variable in read.php. 
We'll assign $_GET['id'] to a variable, bind it to the name of id, and look for the id with the WHERE clause.
public/update-single.php
if (isset($_GET['id'])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $id = $_GET['id'];
    $sql = "SELECT * FROM users WHERE id = :id";
    $statement = $connection->prepare($sql);
    $statement->bindValue(':id', $id);
    $statement->execute();
    $user = $statement->fetch(PDO::FETCH_ASSOC);
  } catch(PDOException $error) {
      echo $sql . 
"<br>" . $error->getMessage();
  }
} else {
  echo "Something went wrong!";
  exit;
}
Now we want to display the data, but it's going to be a little different than the previous times we printed out data, because we want to be able to update this data as well. 
How will we update it? With forms and inputs, just like we INSERT and SELECT data.
Below the database code, we'll begin our view for update-single.php. 
Now, since I know the data consists of first name, last name, and so on, I can just manually type it all out as we did before, but there's a more efficient way to get all that data. 
Let's dynamically print out each data column and value as a key/value pair in PHP.
We'll start by writing a foreach loop, but instead of returning the entire associative array in the variable, we'll separate the keys and values into their own variables. 
We'll get the value from the fetch(PDO::FETCH_ASSOC) above.
We're going to put the entire loop inside a form with a submit button.
public/update-single.php
<?php require "templates/header.php"; ?>
<h2>Edit a user</h2>
<form method="post">
  <?php foreach ($user as $key => $value) : ?>
    // print data here
  <?php endforeach; ?>
  <input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
Inside the foreach, we're making a form which will consist of labels and inputs. 
Each label will be a column name from the database.
<label for="<?php echo $key; ?>">
  <?php echo ucfirst($key); ?>
</label>
Each value will be the value of an input. 
We'll be using the key as the name and id of the input, and the value as the value. 
I'm also adding a ternary (quick conditional statement) to make the input "readonly" if the key name is id, as it should not be editable.
<input
  type="text"
  name="<?php echo $key; ?>"
  id="<?php echo $key; ?>"
  value="<?php echo escape($value); ?>">
  <?php echo ($key === 'id' ? 'readonly' : null); ?>>
Now the entire form looks like this.
public/update-single.php
<form method="post">
    <?php foreach ($user as $key => $value) : ?>
      <label for="<?php echo $key; ?>"><?php echo ucfirst($key); ?></label>
      <input type="text" name="<?php echo $key; ?>" id="<?php echo $key; ?>" value="<?php echo escape($value); ?>" <?php echo ($key === 'id' ? 'readonly' : null); ?>>
    <?php endforeach; ?>
    <input type="submit" name="submit" value="Submit">
</form>
Okay, that was quite a bit of work, but now look what we have!

    
The single edit page now has editable fields for each value. 
However, our code at this point is only to display the data - we need to add another section to process the code after the submit button has been pressed.
At the top of update-single.php, right below the two required files, check if the form has been submitted, and begin the try/catch block.
if (isset($_POST['submit'])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    // run update query
  } catch(PDOException $error) {
    echo $sql . 
"<br>" . $error->getMessage();
  }
}
We want to use the UPDATE clause to SET each value to the new value. 
The literal code for our case looks like this:
   UPDATE users
     SET id = :id,
         firstname = :firstname,
         lastname = :lastname,
         email = :email,
         age = :age,
         location = :location,
         date = :date
   WHERE id = :id
That's all we need to update the values now! I'm just going to quickly add in some code to display that everything has been updated on the front end.
<?php if (isset($_POST['submit']) && $statement) : ?>
  <?php echo escape($_POST['firstname']); ?> successfully updated.
<?php endif; ?>
Here is the entirety of the file we just created.
public/update-single.php
<?php
/**
  * Use an HTML form to edit an entry in the
  * users table.
  *
  */
require "../config.php";
require "../common.php";
if (isset($_POST['submit'])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $user =[
      "id"        => $_POST['id'],
      "firstname" => $_POST['firstname'],
      "lastname"  => $_POST['lastname'],
      "email"     => $_POST['email'],
      "age"       => $_POST['age'],
      "location"  => $_POST['location'],
      "date"      => $_POST['date']
    ];
    $sql = "UPDATE users
            SET id = :id,
              firstname = :firstname,
              lastname = :lastname,
              email = :email,
              age = :age,
              location = :location,
              date = :date
            WHERE id = :id";
  $statement = $connection->prepare($sql);
  $statement->execute($user);
  } catch(PDOException $error) {
      echo $sql . 
"<br>" . $error->getMessage();
  }
}
if (isset($_GET['id'])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $id = $_GET['id'];
    $sql = "SELECT * FROM users WHERE id = :id";
    $statement = $connection->prepare($sql);
    $statement->bindValue(':id', $id);
    $statement->execute();
    $user = $statement->fetch(PDO::FETCH_ASSOC);
  } catch(PDOException $error) {
      echo $sql . 
"<br>" . $error->getMessage();
  }
} else {
    echo "Something went wrong!";
    exit;
}
?>
<?php require "templates/header.php"; ?>
<?php if (isset($_POST['submit']) && $statement) : ?>
  <?php echo escape($_POST['firstname']); ?> successfully updated.
<?php endif; ?>
<h2>Edit a user</h2>
<form method="post">
    <?php foreach ($user as $key => $value) : ?>
      <label for="<?php echo $key; ?>"><?php echo ucfirst($key); ?></label>
      <input type="text" name="<?php echo $key; ?>" id="<?php echo $key; ?>" value="<?php echo escape($value); ?>" <?php echo ($key === 'id' ? 'readonly' : null); ?>>
    <?php endforeach; ?>
    <input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
As a test, I updated the age value to make sure it worked.

    
Now that we've completed the update process, it's time to move on to deleting, which is much simpler.
Step 4: Deleting entries from a database
Back in index.php, let's add an entry for delete. 
We could put this in the update file, but for the sake of continuity and finishing the acronym, let's just make it into a new file.
public/index.php
<ul>
  <li>
    <a href="create.php"><strong>Create</strong></a> - add a user
  </li>
  <li>
    <a href="read.php"><strong>Read</strong></a> - find a user
  </li>
  <li>
    <a href="update.php"><strong>Update</strong></a> - edit a user
  </li>
  <li>
    <a href="delete.php"><strong>Delete</strong></a> - delete a user
  </li>
</ul>
There's nothing new to learn with the DELETE statement. 
Let's copy the code from update.php over, but change "edit" to "delete". 
We'll have the delete link to to the same URL with a query string added, instead of a single page for deletion.
<td><a href="delete.php?id=<?php echo escape($row["id"]); ?>">Delete</a></td>
The DELETE statement is just like SELECT, and we'll check for the $_GET superglobal again. 
If the proper id is loaded into the URL, PHP will delete that user.
public/delete.php
<?php
/**
  * Delete a user
  */
require "../config.php";
require "../common.php";
if (isset($_GET["id"])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $id = $_GET["id"];
    $sql = "DELETE FROM users WHERE id = :id";
    $statement = $connection->prepare($sql);
    $statement->bindValue(':id', $id);
    $statement->execute();
    $success = "User successfully deleted";
  } catch(PDOException $error) {
    echo $sql . 
"<br>" . $error->getMessage();
  }
}
try {
  $connection = new PDO($dsn, $username, $password, $options);
  $sql = "SELECT * FROM users";
  $statement = $connection->prepare($sql);
  $statement->execute();
  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . 
"<br>" . $error->getMessage();
}
?>
<?php require "templates/header.php"; ?>
<h2>Delete users</h2>
<?php if ($success) echo $success; ?>
<table>
  <thead>
    <tr>
      <th>#</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Email Address</th>
      <th>Age</th>
      <th>Location</th>
      <th>Date</th>
      <th>Delete</th>
    </tr>
  </thead>
  <tbody>
  <?php foreach ($result as $row) : ?>
    <tr>
      <td><?php echo escape($row["id"]); ?></td>
      <td><?php echo escape($row["firstname"]); ?></td>
      <td><?php echo escape($row["lastname"]); ?></td>
      <td><?php echo escape($row["email"]); ?></td>
      <td><?php echo escape($row["age"]); ?></td>
      <td><?php echo escape($row["location"]); ?></td>
      <td><?php echo escape($row["date"]); ?> </td>
      <td><a href="delete.php?id=<?php echo escape($row["id"]); ?>">Delete</a></td>
    </tr>
  <?php endforeach; ?>
  </tbody>
</table>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
Here's the page after I deleted two users.

    
Often, in a real app, data won't be permanently deleted from the database. 
The users might have a boolean table that defines them as "active" or "inactive" users instead of actually deleting the data. 
Nonetheless, DELETE is important to know, and it can be used along with SELECT to delete than insert new data as opposed to updating it with UPDATE.
Adding CSRF Protection
As a final step, we want to take security into consideration. 
We will generate a CSRF token and add it to the user's session.
CSRF stands for Cross-Site Request Forgery, and is a way an attacker can trick a browser into executing a malicious action.
In order to prevent this, we will test the CSRF token in the session against a value in a hidden input. 
If they match, the code will execute. 
If not, the code will exit.
To add this in, we'll go back to common.php, generate the CSRF and assign it to $_SESSION['csrf'].
common.php
session_start();
if (empty($_SESSION['csrf'])) {
  if (function_exists('random_bytes')) {
    $_SESSION['csrf'] = bin2hex(random_bytes(32));
  } else if (function_exists('mcrypt_create_iv')) {
    $_SESSION['csrf'] = bin2hex(mcrypt_create_iv(32, MCRYPT_DEV_URANDOM));
  } else {
    $_SESSION['csrf'] = bin2hex(openssl_random_pseudo_bytes(32));
  }
}
Now at the top of all our public/ files, we'll add the following code in:
require "../config.php";
require "../common.php";
if (isset($_POST['submit'])) {
  if (!hash_equals($_SESSION['csrf'], $_POST['csrf'])) die();
Finally, we'll add the value into the input on each form.
<input name="csrf" type="hidden" value="<?php echo escape($_SESSION['csrf']); ?>">
Contribution by djhayman.
Conclusion
That was a lot of information. 
If you got lost somewhere along the way, I don't blame you! There's a reason few tutorials venture into this territory. 
Fortunately, all the code is up on GitHub. 
All you have to do is clone or download the entire repository into the root of your server, run the install script, and you're good to go with all the CRUD operations!
View on GitHub